package railRoad.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import railRoad.Route;
import railRoad.dao.interfaces.IDAO;
import railRoad.db.DBConectionPool;
import railRoad.exception.DBException;

/**
 * Implementation of {@link#IDAO} for {@link#Route}
 * 
 * @see IDAO
 * @see Route
 * @author Rukin
 * 
 */
public class RouteDAO implements IDAO<Route> {

	DBConectionPool conectionPool = DBConectionPool.getInstance();

	public Route select(int id) throws DBException {
		Route route = null;
		Connection connection = null;
		try {
			connection = conectionPool.getConnection();
			connection.setAutoCommit(false);

			Statement statement = connection.createStatement();
			ResultSet resultSet = statement
					.executeQuery("SELECT * FROM ROUTE WHERE ROUTE_ID = " + id);

			while (resultSet.next()) {
				int rId = resultSet.getInt("ROUTE_ID");
				int avalTickets = resultSet.getInt("AVAILABLE_TICKETS");

				route = new Route(rId, avalTickets);
			}
		} catch (SQLException e) {
			throw new DBException(e.getMessage());
		} finally {
			conectionPool.close(connection);
		}// finally

		return route;
	}// select

	public int insert(Route route, int parrentId) throws DBException {
		Connection connection = null;
		int result = -1;
		try {
			connection = conectionPool.getConnection();
			connection.setAutoCommit(false);

			PreparedStatement preparedStatement = connection
					.prepareStatement("INSERT INTO ROUTE( AVAILABLE_TICKETS ) VALUES (?)");

			preparedStatement.setInt(1, route.getAvailableTicket());

			result = preparedStatement.executeUpdate();

			connection.commit();

			preparedStatement = connection
					.prepareStatement("SELECT MAX(ROUTE_ID) as ID FROM ROUTE");

			ResultSet resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				route.setRoute_id(resultSet.getInt("ID"));
			}

		} catch (SQLException e) {
			throw new DBException(e.getMessage());
		} finally {
			conectionPool.close(connection);
		}// finally

		return result;
	}// insert

	public int update(Route route) throws DBException {
		Connection connection = null;

		int result = -1;
		try {
			connection = conectionPool.getConnection();
			connection.setAutoCommit(false);

			PreparedStatement preparedStatement = connection
					.prepareStatement("UPDATE ROUTE r SET r.AVAILABLE_TICKETS = ? WHERE r.ROUTE_ID = ?");

			preparedStatement.setInt(1, route.getAvailableTicket());
			preparedStatement.setInt(2, route.getRoute_id());

			result = preparedStatement.executeUpdate();

			connection.commit();
		} catch (SQLException e) {
			throw new DBException(e.getMessage());
		} finally {
			conectionPool.close(connection);
		}// finally

		return result;
	}// update

	public int delete(Route route) throws DBException {
		Connection connection = null;

		int result = -1;
		try {
			connection = conectionPool.getConnection();

			int id = route.getRoute_id();

			PreparedStatement preparedStatement = connection
					.prepareStatement("delete from ticket where ticket.route_id = "
							+ id
							+ "; delete from station where station.route_id =  "
							+ id + "; delete from route where route_id = " + id);

			result = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			throw new DBException(e.getMessage());
		} finally {
			conectionPool.close(connection);
		}// finally

		return result;
	}// delete

	public Route select(String field) throws DBException {
		// TODO Auto-generated method stub
		return null;
	}

}
